DROP TABLE LIME_ROLES CASCADE CONSTRAINTS;
DROP TABLE LIME_USERS CASCADE CONSTRAINTS;
DROP TABLE LIME_DATABASE_TYPES CASCADE CONSTRAINTS;
DROP TABLE LIME_DATABASES CASCADE CONSTRAINTS;
DROP TABLE LIME_RIGHTS CASCADE CONSTRAINTS;
DROP TABLE LIME_CONNECTIONS CASCADE CONSTRAINTS;
DROP SEQUENCE LIME_USERS_SEQUENCE;
DROP SEQUENCE LIME_DATABASES_SEQUENCE;
DROP SEQUENCE LIME_CONNECTIONS_SEQUENCE;


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

---------------------------------------
-- ROLES
---------------------------------------
CREATE TABLE LIME_ROLES
(
	ROLE VARCHAR2(255) NOT NULL
);
ALTER TABLE LIME_ROLES ADD CONSTRAINT pk_lime_roles PRIMARY KEY (ROLE);


---------------------------------------
-- USERS
---------------------------------------
CREATE TABLE LIME_USERS
(
	ID NUMBER NOT NULL,
	LOGIN VARCHAR2(255) NOT NULL UNIQUE,
	PASSWORD VARCHAR2(255) NOT NULL,
	ROLE VARCHAR2(255) NOT NULL
);
ALTER TABLE LIME_USERS ADD CONSTRAINT pk_lime_users PRIMARY KEY (ID);
ALTER TABLE LIME_USERS ADD CONSTRAINT fk_lime_users_roles FOREIGN KEY (ROLE) REFERENCES LIME_ROLES(ROLE);

--AUTO INCREMENT
CREATE SEQUENCE LIME_USERS_SEQUENCE;
CREATE OR REPLACE TRIGGER LIME_USERS_SEQUENCE_INC
BEFORE INSERT ON LIME_USERS
FOR EACH ROW
BEGIN
  SELECT LIME_USERS_SEQUENCE.NEXTVAL INTO :new.ID FROM dual;
END;
/

---------------------------------------
-- DATABASE TYPES
---------------------------------------
CREATE TABLE LIME_DATABASE_TYPES
(
	TYPENAME VARCHAR2(255) NOT NULL
);
ALTER TABLE LIME_DATABASE_TYPES ADD CONSTRAINT pk_lime_database_types PRIMARY KEY (TYPENAME);

---------------------------------------
-- DATABASES
---------------------------------------
CREATE TABLE LIME_DATABASES
(
	ID NUMBER NOT NULL,
	NAME VARCHAR2(255) NOT NULL,
	TYPENAME VARCHAR2(255) NOT NULL,
	HOSTNAME VARCHAR2(255) NOT NULL,
	PORT NUMBER NOT NULL,
	SID VARCHAR2(255) NOT NULL
);
ALTER TABLE LIME_DATABASES ADD CONSTRAINT pk_lime_databases PRIMARY KEY (ID);
ALTER TABLE LIME_DATABASES ADD CONSTRAINT fk_lime_databases_typename FOREIGN KEY (TYPENAME) REFERENCES LIME_DATABASE_TYPES(TYPENAME);

--AUTO INCREMENT
CREATE SEQUENCE LIME_DATABASES_SEQUENCE;
CREATE OR REPLACE TRIGGER LIME_DATABASES_SEQUENCE_INC
BEFORE INSERT ON LIME_DATABASES
FOR EACH ROW
BEGIN
  SELECT LIME_DATABASES_SEQUENCE.NEXTVAL INTO :new.ID FROM dual;
END;
/

---------------------------------------
-- RIGHTS
---------------------------------------
CREATE TABLE LIME_RIGHTS
(
	USERID NUMBER NOT NULL,
	DATABASEID NUMBER NOT NULL
);
ALTER TABLE LIME_RIGHTS ADD CONSTRAINT pk_lime_rights PRIMARY KEY (USERID,DATABASEID);
ALTER TABLE LIME_RIGHTS ADD CONSTRAINT fk_lime_rights_userid FOREIGN KEY (USERID) REFERENCES LIME_USERS(ID);
ALTER TABLE LIME_RIGHTS ADD CONSTRAINT fk_lime_rights_databaseid FOREIGN KEY (DATABASEID) REFERENCES LIME_DATABASES(ID);

---------------------------------------
-- CONNECTION
---------------------------------------
CREATE TABLE LIME_CONNECTIONS
(
  ID NUMBER NOT NULL,
  USERID NUMBER NOT NULL,
  CONNECTION_TIME DATE NOT NULL
);
ALTER TABLE LIME_CONNECTIONS ADD CONSTRAINT pk_lime_connections PRIMARY KEY (ID);

--AUTO INCREMENT
CREATE SEQUENCE LIME_CONNECTIONS_SEQUENCE;
CREATE OR REPLACE TRIGGER LIME_CONNECTIONS_SEQUENCE_INC
BEFORE INSERT ON LIME_CONNECTIONS
FOR EACH ROW
BEGIN
  SELECT LIME_CONNECTIONS_SEQUENCE.NEXTVAL INTO :new.ID FROM dual;
END;
/

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

INSERT INTO LIME_ROLES VALUES ('GESTIONNAIRE');
INSERT INTO LIME_ROLES VALUES ('ADMINISTRATEUR');
INSERT INTO LIME_ROLES VALUES ('DEVELOPPEUR');
	
INSERT INTO LIME_USERS VALUES (0, 'gestion', '1d0258c2440a8d19e716292b231e3190', 'GESTIONNAIRE');
INSERT INTO LIME_USERS VALUES (0, 'admin', '1d0258c2440a8d19e716292b231e3190', 'ADMINISTRATEUR');
INSERT INTO LIME_USERS VALUES (0, 'develop', '1d0258c2440a8d19e716292b231e3190', 'DEVELOPPEUR');

INSERT INTO LIME_DATABASE_TYPES VALUES ('ORACLE');
INSERT INTO LIME_DATABASE_TYPES VALUES ('MYSQL');

INSERT INTO LIME_DATABASES VALUES (0, 'Oracle 1', 'ORACLE', 'localhost', 1521, 'xe');
INSERT INTO LIME_DATABASES VALUES (1, 'MySQL 1', 'MYSQL', 'localhost', 3306, 'sakila');

INSERT INTO LIME_RIGHTS VALUES (1,1);
INSERT INTO LIME_RIGHTS VALUES (1,2);
INSERT INTO LIME_RIGHTS VALUES (2,1);

commit;
	